package com.sit.estatemanagementserver.dao;

import com.sit.estatemanagementserver.enity.FacilityRental;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

@Repository
public class FacilityRentalDao {
    @Autowired
    DatabaseManager db;

    // SQLite不支持DATETIME类型，所以我们用TEXT存储ISO格式的字符串
    private static final DateTimeFormatter formatter = DateTimeFormatter.ISO_LOCAL_DATE_TIME;

    // 重构: 适配新的 FacilityRental 构造函数, 处理 LocalDateTime
    private FacilityRental resultSetToFacilityRental(ResultSet rs) throws SQLException {
        return new FacilityRental(
                rs.getString(DatabaseContract.FacilityRentalEntry.COLUMN_WORK_ORDER_ID),
                rs.getString(DatabaseContract.FacilityRentalEntry.COLUMN_FACILITY_ID_FK),
                rs.getString(DatabaseContract.FacilityRentalEntry.COLUMN_OWNER_PHONE_FK),
                rs.getString(DatabaseContract.FacilityRentalEntry.COLUMN_RENTAL_INFO),
                LocalDateTime.parse(rs.getString(DatabaseContract.FacilityRentalEntry.COLUMN_START_TIME), formatter),
                LocalDateTime.parse(rs.getString(DatabaseContract.FacilityRentalEntry.COLUMN_END_TIME), formatter)
        );
    }

    public boolean addFacilityRental(FacilityRental rental) {
        // 重构: 更新SQL语句以匹配新表结构
        String sql = "INSERT INTO " + DatabaseContract.FacilityRentalEntry.TABLE_NAME + "(" +
                DatabaseContract.FacilityRentalEntry.COLUMN_WORK_ORDER_ID + "," +
                DatabaseContract.FacilityRentalEntry.COLUMN_FACILITY_ID_FK + "," +
                DatabaseContract.FacilityRentalEntry.COLUMN_OWNER_PHONE_FK + "," +
                DatabaseContract.FacilityRentalEntry.COLUMN_RENTAL_INFO + "," +
                DatabaseContract.FacilityRentalEntry.COLUMN_START_TIME + "," +
                DatabaseContract.FacilityRentalEntry.COLUMN_END_TIME + ") VALUES(?,?,?,?,?,?)";
        try (Connection conn = db.connect(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, rental.getWorkOrderId());
            pstmt.setString(2, rental.getFacilityId());
            pstmt.setString(3, rental.getOwnerPhone());
            pstmt.setString(4, rental.getRentalInfo());
            // 重构: 将LocalDateTime格式化为字符串存入数据库
            pstmt.setString(5, rental.getStartTime().format(formatter));
            pstmt.setString(6, rental.getEndTime().format(formatter));
            pstmt.executeUpdate();
            return true;
        } catch (SQLException e) {
            System.err.println(DatabaseManager.TAG + ": Error adding facility rental: " + e.getMessage());
            return false;
        }
    }

    public FacilityRental getFacilityRental(String workOrderId) {
        String sql = "SELECT * FROM " + DatabaseContract.FacilityRentalEntry.TABLE_NAME + " WHERE " +
                DatabaseContract.FacilityRentalEntry.COLUMN_WORK_ORDER_ID + " = ?";
        try (Connection conn = db.connect(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, workOrderId);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return resultSetToFacilityRental(rs);
                }
            }
        } catch (SQLException e) {
            System.err.println(DatabaseManager.TAG + ": Error getting facility rental with id '" + workOrderId + "': " + e.getMessage());
        }
        return null;
    }

    // ... getAll, getByOwner, getByFacility 等查询方法的逻辑不变, 仅通过resultSetToFacilityRental适配新实体

    public boolean updateFacilityRental(FacilityRental rental) {
        String sql = "UPDATE " + DatabaseContract.FacilityRentalEntry.TABLE_NAME + " SET " +
                DatabaseContract.FacilityRentalEntry.COLUMN_FACILITY_ID_FK + " = ?, " +
                DatabaseContract.FacilityRentalEntry.COLUMN_OWNER_PHONE_FK + " = ?, " +
                DatabaseContract.FacilityRentalEntry.COLUMN_RENTAL_INFO + " = ?, " +
                DatabaseContract.FacilityRentalEntry.COLUMN_START_TIME + " = ?, " +
                DatabaseContract.FacilityRentalEntry.COLUMN_END_TIME + " = ? WHERE " +
                DatabaseContract.FacilityRentalEntry.COLUMN_WORK_ORDER_ID + " = ?";
        try (Connection conn = db.connect(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, rental.getFacilityId());
            pstmt.setString(2, rental.getOwnerPhone());
            pstmt.setString(3, rental.getRentalInfo());
            pstmt.setString(4, rental.getStartTime().format(formatter));
            pstmt.setString(5, rental.getEndTime().format(formatter));
            pstmt.setString(6, rental.getWorkOrderId());
            int affectedRows = pstmt.executeUpdate();
            return affectedRows > 0;
        } catch (SQLException e) {
            System.err.println(DatabaseManager.TAG + ": Error updating facility rental with id '" + rental.getWorkOrderId() + "': " + e.getMessage());
            return false;
        }
    }

    public boolean deleteFacilityRental(String workOrderId) {
        String sql = "DELETE FROM " + DatabaseContract.FacilityRentalEntry.TABLE_NAME + " WHERE " +
                DatabaseContract.FacilityRentalEntry.COLUMN_WORK_ORDER_ID + " = ?";
        try (Connection conn = db.connect(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, workOrderId);
            int affectedRows = pstmt.executeUpdate();
            return affectedRows > 0;
        } catch (SQLException e) {
            System.err.println(DatabaseManager.TAG + ": Error deleting facility rental with id '" + workOrderId + "': " + e.getMessage());
            return false;
        }
    }

    /**
     * 新增方法: 查找与给定时间段重叠的设施预定记录
     * @param facilityId 设施ID
     * @param startTime  新预定的开始时间
     * @param endTime    新预定的结束时间
     * @return 重叠的预定记录列表
     */
    public List<FacilityRental> findOverlappingRentals(String facilityId, LocalDateTime startTime, LocalDateTime endTime) {
        List<FacilityRental> overlappingRentals = new ArrayList<>();
        // 核心SQL逻辑: 如果一个已有预定的结束时间在新预定的开始时间之后，
        // 并且其开始时间在新预定的结束时间之前，则它们重叠。
        String sql = "SELECT * FROM " + DatabaseContract.FacilityRentalEntry.TABLE_NAME + " WHERE "
                + DatabaseContract.FacilityRentalEntry.COLUMN_FACILITY_ID_FK + " = ? AND "
                + DatabaseContract.FacilityRentalEntry.COLUMN_START_TIME + " < ? AND "
                + DatabaseContract.FacilityRentalEntry.COLUMN_END_TIME + " > ?";

        try (Connection conn = db.connect(); PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, facilityId);
            pstmt.setString(2, endTime.format(formatter));   // 注意参数顺序
            pstmt.setString(3, startTime.format(formatter)); // 注意参数顺序
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    overlappingRentals.add(resultSetToFacilityRental(rs));
                }
            }
        } catch (SQLException e) {
            System.err.println(DatabaseManager.TAG + ": Error finding overlapping rentals: " + e.getMessage());
        }
        return overlappingRentals;
    }
}